/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Connection;

import edu.fpt.common.Budget;
import edu.fpt.common.HibernateUtil;
import edu.fpt.common.Plans;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author Admin
 */
public class PlanDAL {

    public static Plans searchBudget(String id) {
        Session session = null;
        Transaction tran = null;
        try {
            session = (Session) HibernateUtil.getSessionFactory().openSession();
            tran = session.beginTransaction();

            Query query = session.createQuery("from Plans where ID = :code");
            query.setParameter("code", id);
            List list = query.list();

            tran.commit();

            if (list.size() > 0) {
                return (Plans) list.get(0);
            } else {
                return null;
            }
        } finally {
            session.close();
        }
    }

    public static void update(int id, String name, String type, String amount, Date occurenceTime, Date createdTime, Date preTime, String kind) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;

        String sql = "UPDATE Plans SET Name=?,Type=?,Amount=?,OccurenceTime=?,CreatedTime=?,PreTime=?,Kind=? WHERE ID=?";
        try {
            con = ConnectionUtitility.openConnection();
            pstmt = con.prepareStatement(sql);


            pstmt.setString(1, name);
            pstmt.setString(2, type);
            pstmt.setString(3, amount);
            if (occurenceTime != null) {
                pstmt.setDate(5, null);
                pstmt.setDate(6, null);
                java.sql.Date sqlDateHappen = new java.sql.Date(occurenceTime.getTime());
                pstmt.setDate(4, sqlDateHappen);
            } else {
                pstmt.setDate(4, null);
                java.sql.Date sqlDateBegin = new java.sql.Date(createdTime.getTime());
                java.sql.Date sqlDateEnd = new java.sql.Date(preTime.getTime());
                pstmt.setDate(5, sqlDateBegin);
                pstmt.setDate(6, sqlDateEnd);
            }






            pstmt.setString(7, kind);
            pstmt.setInt(8, id);


            pstmt.executeUpdate();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception e) {
            }
        }


    }

    public static void insertOnce(String name, String type, String amount, Date occurenceTime, String kind) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;

        String sql = "Insert into Plans(Name,Type,Amount,OccurenceTime,Kind)"
                + " values(?,?,?,?,?)";
        try {
            con = ConnectionUtitility.openConnection();
            pstmt = con.prepareStatement(sql);

            pstmt.setString(1, name);
            pstmt.setString(2, type);
            pstmt.setString(3, amount);

            java.sql.Date sqlDateHappen = new java.sql.Date(occurenceTime.getTime());
            pstmt.setDate(4, sqlDateHappen);

            pstmt.setString(5, kind);


            pstmt.executeUpdate();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception e) {
            }
        }


    }

    public static void insertDaily(String name, String type, String amount, Date createdTime, Date preTime, String kind) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;

        String sql = "Insert into Plans(Name,Type,Amount,CreatedTime,PreTime,Kind)"
                + " values(?,?,?,?,?,?)";
        try {
            con = ConnectionUtitility.openConnection();
            pstmt = con.prepareStatement(sql);

            pstmt.setString(1, name);
            pstmt.setString(2, type);
            pstmt.setString(3, amount);


            java.sql.Date sqlDateBegin = new java.sql.Date(createdTime.getTime());
            java.sql.Date sqlDateEnd = new java.sql.Date(preTime.getTime());
            pstmt.setDate(4, sqlDateBegin);
            pstmt.setDate(5, sqlDateEnd);

            pstmt.setString(6, kind);


            pstmt.executeUpdate();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
    }

    public static Collection<PlanEntity> listAllDailyIncome(Date dateBegin, Date dateEnd) throws ClassNotFoundException, SQLException {
        java.sql.Date sqlDateBegin = new java.sql.Date(dateBegin.getTime());
        java.sql.Date sqlDateEnd = new java.sql.Date(dateEnd.getTime());

        Collection<PlanEntity> list = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select* from Plans where CreatedTime>=? and CreatedTime<? and Type='Daily' and Kind='Income'";
        try {
            con = ConnectionUtitility.openConnection();

            pstmt = con.prepareStatement(sql);
            pstmt.setDate(1, sqlDateBegin);
            pstmt.setDate(2, sqlDateEnd);


            rs = pstmt.executeQuery();
            list = new ArrayList<PlanEntity>();
            while (rs.next()) {

                PlanEntity entity = new PlanEntity();
                entity.setId(rs.getInt("ID"));
                entity.setName(rs.getString("Name"));
                entity.setType(rs.getString("Type"));
                entity.setAmount(rs.getString("Amount"));
                entity.setCreatedTime(rs.getDate("CreatedTime"));
                entity.setPreTime(rs.getDate("PreTime"));
                entity.setOccurenceTime(rs.getDate("OccurenceTime"));
                entity.setKind(rs.getString("Kind"));
                list.add(entity);

            }
        } finally {
            try {
                pstmt.close();
                con.close();

            } catch (Exception e) {
            }
            return list;
        }


    }

    public static Collection<PlanEntity> listAllOnceIncome(Date dateBegin, Date dateEnd) throws ClassNotFoundException, SQLException {
        java.sql.Date sqlDateBegin = new java.sql.Date(dateBegin.getTime());
        java.sql.Date sqlDateEnd = new java.sql.Date(dateEnd.getTime());

        Collection<PlanEntity> list = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select* from Plans where OccurenceTime>=? and OccurenceTime<=? and Type='Once' and Kind='Income'";
        try {
            con = ConnectionUtitility.openConnection();

            pstmt = con.prepareStatement(sql);
            pstmt.setDate(1, sqlDateBegin);
            pstmt.setDate(2, sqlDateEnd);


            rs = pstmt.executeQuery();
            list = new ArrayList<PlanEntity>();
            while (rs.next()) {

                PlanEntity entity = new PlanEntity();
                entity.setId(rs.getInt("ID"));
                entity.setName(rs.getString("Name"));
                entity.setType(rs.getString("Type"));
                entity.setAmount(rs.getString("Amount"));
                entity.setCreatedTime(rs.getDate("CreatedTime"));
                entity.setPreTime(rs.getDate("PreTime"));
                entity.setOccurenceTime(rs.getDate("OccurenceTime"));
                entity.setKind(rs.getString("Kind"));
                list.add(entity);

            }
        } finally {
            try {
                pstmt.close();
                con.close();

            } catch (Exception e) {
            }
            return list;
        }


    }

    public static Collection<PlanEntity> listAllDailyOutCome(Date dateBegin, Date dateEnd) throws ClassNotFoundException, SQLException {
        java.sql.Date sqlDateBegin = new java.sql.Date(dateBegin.getTime());
        java.sql.Date sqlDateEnd = new java.sql.Date(dateEnd.getTime());

        Collection<PlanEntity> list = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select* from Plans where CreatedTime>=? and CreatedTime<? and Type='Daily' and Kind='Outcome'";
        try {
            con = ConnectionUtitility.openConnection();

            pstmt = con.prepareStatement(sql);
            pstmt.setDate(1, sqlDateBegin);
            pstmt.setDate(2, sqlDateEnd);


            rs = pstmt.executeQuery();
            list = new ArrayList<PlanEntity>();
            while (rs.next()) {

                PlanEntity entity = new PlanEntity();
                entity.setId(rs.getInt("ID"));
                entity.setName(rs.getString("Name"));
                entity.setType(rs.getString("Type"));
                entity.setAmount(rs.getString("Amount"));
                entity.setCreatedTime(rs.getDate("CreatedTime"));
                entity.setPreTime(rs.getDate("PreTime"));
                entity.setOccurenceTime(rs.getDate("OccurenceTime"));
                entity.setKind(rs.getString("Kind"));
                list.add(entity);

            }
        } finally {
            try {
                pstmt.close();
                con.close();

            } catch (Exception e) {
            }
            return list;
        }


    }

    public static Collection<PlanEntity> listAllOnceOutcome(Date dateBegin, Date dateEnd) throws ClassNotFoundException, SQLException {
        java.sql.Date sqlDateBegin = new java.sql.Date(dateBegin.getTime());
        java.sql.Date sqlDateEnd = new java.sql.Date(dateEnd.getTime());

        Collection<PlanEntity> list = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select* from Plans where OccurenceTime>=? and OccurenceTime<=? and Type='Once' and Kind='Outcome'";
        try {
            con = ConnectionUtitility.openConnection();

            pstmt = con.prepareStatement(sql);
            pstmt.setDate(1, sqlDateBegin);
            pstmt.setDate(2, sqlDateEnd);


            rs = pstmt.executeQuery();
            list = new ArrayList<PlanEntity>();
            while (rs.next()) {

                PlanEntity entity = new PlanEntity();
                entity.setId(rs.getInt("ID"));
                entity.setName(rs.getString("Name"));
                entity.setType(rs.getString("Type"));
                entity.setAmount(rs.getString("Amount"));
                entity.setCreatedTime(rs.getDate("CreatedTime"));
                entity.setPreTime(rs.getDate("PreTime"));
                entity.setOccurenceTime(rs.getDate("OccurenceTime"));
                entity.setKind(rs.getString("Kind"));
                list.add(entity);

            }
        } finally {
            try {
                pstmt.close();
                con.close();

            } catch (Exception e) {
            }
            return list;
        }


    }

    public static void delete(Integer id) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;

        String sql = "delete from Plans where id=? ";
        try {
            con = ConnectionUtitility.openConnection();
            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, id);


            pstmt.executeUpdate();


        } finally {
            try {
                pstmt.close();
                con.close();

            } catch (Exception e) {
            }

        }
    }
//    public static void insert(String name, String type, String amount, Date createdTime, Date preTime, Date occurenceTime, String kind) throws ClassNotFoundException, SQLException {
//        Connection con = null;
//        PreparedStatement pstmt = null;
//
//        String sql = "Insert into Plans(Name,Type,Amount,CreatedTime,PreTime,OccurenceTime,Kind)"
//                + " values(?,?,?,?,?,?,?)";
//        try {
//            con = ConnectionUtitility.openConnection();
//            pstmt = con.prepareStatement(sql);
//
//            pstmt.setString(1, name);
//            pstmt.setString(2, type);
//            pstmt.setString(3, amount);
////            pstmt.setString(4,createdTime.toString());
////            pstmt.setString(5,preTime.toString());
////            pstmt.setString(6,occurenceTime.toString());
//            java.sql.Date sqlDateBegin = new java.sql.Date(createdTime.getTime());
//            java.sql.Date sqlDateEnd = new java.sql.Date(preTime.getTime());
//            java.sql.Date sqlDateHappen = new java.sql.Date(occurenceTime.getTime());
//            pstmt.setDate(4,sqlDateBegin );
//            pstmt.setDate(5, sqlDateEnd);
//            pstmt.setDate(6, sqlDateHappen);
//            pstmt.setString(7, kind);
//
//
//            pstmt.executeUpdate();
//        } finally {
//            try {
//                pstmt.close();
//                con.close();
//            } catch (Exception e) {
//            }
//        }
//
//
//    }
}
